<?php

global $modx;
$prefix = 'foxyback_';

$modx->db->query("CREATE TABLE IF NOT EXISTS ".$modx->getFullTableName($prefix . 'address')." (
  `address_id` int(10) unsigned NOT NULL auto_increment,
  `customer_id` int(10) unsigned NOT NULL default '0',
  `transaction_id` int(10) unsigned default NULL,
  `address_type` enum('BILLING','SHIPPING') NOT NULL default 'BILLING',
  `first_name` varchar(50) NOT NULL default '',
  `last_name` varchar(50) NOT NULL default '',
  `company` varchar(50) default NULL,
  `address1` varchar(100) NOT NULL default '',
  `address2` varchar(100) default NULL,
  `city` varchar(50) NOT NULL default '',
  `state` varchar(50) NOT NULL default '',
  `postal_code` varchar(20) NOT NULL default '',
  `country` varchar(50) NOT NULL default '',
  `phone` varchar(50) NOT NULL default '',
  `service_description` varchar(100) default NULL,
  `subtotal` decimal(10,2) default NULL,
  `tax_total` decimal(10,2) default NULL,
  `shipping_total` decimal(10,2) default NULL,
  `total` decimal(10,2) default NULL,
  PRIMARY KEY  (`address_id`),
  KEY `customer_id` (`customer_id`,`transaction_id`),
  KEY `first_name` (`first_name`,`last_name`)
)");

$modx->db->query("CREATE TABLE IF NOT EXISTS ".$modx->getFullTableName($prefix . 'customer')." (
  `customer_id` int(10) unsigned NOT NULL auto_increment,
  `email` varchar(100) NOT NULL default '',
  `ip_address` varchar(15) default NULL,
  `password` varchar(100) default NULL,
  `address_id` int(10) unsigned NOT NULL COMMENT 'Ties the customer to their primary (billing) address',
  `web_user_id` int(10) unsigned default NULL,
  PRIMARY KEY  (`customer_id`),
  KEY `address_id` (`address_id`)
)");

$modx->db->query("CREATE TABLE IF NOT EXISTS ".$modx->getFullTableName($prefix . 'custom_field')." (
  `custom_field_id` int(10) unsigned NOT NULL auto_increment,
  `customer_id` int(10) unsigned NOT NULL default '0',
  `transaction_id` int(10) unsigned NOT NULL default '0',
  `address_id` int(10) unsigned default NULL COMMENT 'Ties to a shipto address',
  `field_name` varchar(100) NOT NULL default '',
  `field_value` text NOT NULL,
  PRIMARY KEY  (`custom_field_id`),
  KEY `customer_id` (`customer_id`,`transaction_id`,`address_id`))");

$modx->db->query("CREATE TABLE IF NOT EXISTS ".$modx->getFullTableName($prefix . 'status')." (
  `status_id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(64) NOT NULL default '',
  `description` text,
  `sequence_id` int(10) unsigned NOT NULL default '0',
  `active` enum('YES','NO') NOT NULL default 'YES',
  PRIMARY KEY  (`status_id`),
  UNIQUE KEY `name` (`name`)
)");

$modx->db->query("CREATE TABLE IF NOT EXISTS ".$modx->getFullTableName($prefix . 'status_log')." (
  `status_log_id` int(10) unsigned NOT NULL auto_increment,
  `customer_id` int(10) unsigned NOT NULL default '0',
  `transaction_id` int(10) unsigned NOT NULL default '0',
  `status_id` int(10) unsigned NOT NULL default '0',
  `add_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `notes` text,
  PRIMARY KEY  (`status_log_id`),
  KEY `customer_id` (`customer_id`,`transaction_id`,`status_id`))");


$modx->db->query("CREATE TABLE IF NOT EXISTS ".$modx->getFullTableName($prefix . 'transaction')." (
  `transaction_id` int(10) unsigned NOT NULL auto_increment,
  `transaction_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `customer_id` int(10) unsigned NOT NULL default '0',
  `purchase_order` varchar(50) NOT NULL default '',
  `product_total` decimal(10,2) NOT NULL default '0.00',
  `tax_total` decimal(10,2) NOT NULL default '0.00',
  `shipping_total` decimal(10,2) NOT NULL default '0.00',
  `order_total` decimal(10,2) NOT NULL default '0.00',
  `order_status_id` int(10) unsigned NOT NULL default '0',
  `flagged` enum('NO','YES') NOT NULL default 'NO',
  `notes` text,
  `ship_tracking_number` varchar(32) default NULL,
  `ship_reference_number` varchar(32) default NULL,
  `ship_date` datetime default NULL,
  `ship_method` varchar(64) default NULL,
  `rma_number` int(10) unsigned default NULL,
  PRIMARY KEY  (`transaction_id`),
  KEY `customer_id` (`customer_id`),
  KEY `order_status_id` (`order_status_id`)
)");


$modx->db->query("CREATE TABLE IF NOT EXISTS ".$modx->getFullTableName($prefix . 'transaction_detail')." (
  `transaction_detail_id` int(10) unsigned NOT NULL auto_increment,
  `transaction_id` int(10) unsigned NOT NULL default '0',
  `product_name` varchar(255) default NULL,
  `product_price` decimal(10,2) NOT NULL default '0.00',
  `product_quantity` int(10) unsigned NOT NULL default '0',
  `product_weight` decimal(10,3) NOT NULL default '0.000',
  `product_code` varchar(50) NOT NULL default '',
  `product_delivery_type` varchar(50) NOT NULL default '',
  `category_description` varchar(100) NOT NULL default '',
  `category_code` varchar(50) NOT NULL default '',
  `subscription_frequency` varchar(20) NOT NULL default '',
  `subscription_startdate` datetime NOT NULL default '0000-00-00 00:00:00',
  `next_transaction_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `shipto` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`transaction_detail_id`),
  KEY `transaction_id` (`transaction_id`)
)");

$modx->db->query("CREATE TABLE IF NOT EXISTS ".$modx->getFullTableName($prefix . 'transaction_detail_option')." (
  `transaction_detail_option_id` int(10) unsigned NOT NULL auto_increment,
  `transaction_detail_id` int(10) unsigned NOT NULL default '0',
  `transaction_id` int(10) unsigned NOT NULL default '0',
  `product_option_name` varchar(100) NOT NULL default '',
  `product_option_value` varchar(255) NOT NULL default '',
  `price_mod` decimal(10,2) NOT NULL default '0.00',
  `weight_mod` decimal(10,3) NOT NULL default '0.000',
  PRIMARY KEY  (`transaction_detail_option_id`),
  KEY `transaction_detail_id` (`transaction_detail_id`,`transaction_id`))");

$modx->db->query("CREATE TABLE IF NOT EXISTS ".$modx->getFullTableName($prefix . 'email_archive')." (
  `email_archive_id` int(10) unsigned NOT NULL auto_increment,
  `transaction_id` int(10) unsigned default NULL,
  `from_address` varchar(100) default NULL,
  `to_address` varchar(100) NOT NULL,
  `subject` varchar(512) default NULL,
  `body` mediumtext,
  `body_html` mediumtext,
  `add_date` datetime NOT NULL,
  `error` text,
  PRIMARY KEY  (`email_archive_id`),
  KEY `transaction_id` (`transaction_id`))");

$modx->db->query("CREATE TABLE IF NOT EXISTS ".$modx->getFullTableName($prefix . 'transaction')." (
  `transaction_id` int(10) unsigned NOT NULL auto_increment,
  `transaction_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `customer_id` int(10) unsigned NOT NULL default '0',
  `purchase_order` varchar(50) NOT NULL default '',
  `product_total` decimal(10,2) NOT NULL default '0.00',
  `tax_total` decimal(10,2) NOT NULL default '0.00',
  `shipping_total` decimal(10,2) NOT NULL default '0.00',
  `order_total` decimal(10,2) NOT NULL default '0.00',
  `order_status_id` int(10) unsigned NOT NULL default '0',
  `flagged` enum('NO','YES') NOT NULL default 'NO',
  `notes` text,
  `ship_tracking_number` varchar(32) default NULL,
  `ship_reference_number` varchar(32) default NULL,
  `ship_date` datetime default NULL,
  `ship_method` varchar(64) default NULL,
  `rma_number` int(10) unsigned default NULL,
  PRIMARY KEY  (`transaction_id`),
  KEY `customer_id` (`customer_id`),
  KEY `order_status_id` (`order_status_id`)
)");
?>
